package org.devocean.groupbuying.dao.impl;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.persistence.TemporalType;

import org.apache.commons.lang.StringUtils;
import org.devocean.groupbuying.dao.ExploredDAO;
import org.devocean.groupbuying.domain.ArticlesLocalization;
import org.devocean.groupbuying.domain.Explored;
import org.hibernate.HibernateException;
import org.springframework.stereotype.Repository;

@Repository
public class ExplorerDAOImpl implements ExploredDAO {

	@PersistenceContext
	private EntityManager em;

	public List<Explored> findAllExploreds() {
		return em.createQuery("SELECT DISTINCT ex FROM Explored ex ",
		// + "LEFT JOIN FETCH di.deal d "
		// + "LEFT JOIN FETCH pr.plan pl "
		// + "LEFT JOIN FETCH pl.product p "
		// + "LEFT JOIN FETCH d.product pd "
		// + "LEFT JOIN FETCH pd.articlesLocalization pdal "
		// + "LEFT JOIN FETCH pdal.articles pdala "
		// + "LEFT JOIN FETCH p.articlesLocalization pal "
		// + "LEFT JOIN FETCH pal.articles pala "
		// + "LEFT JOIN FETCH pl.articlesLocalization plal "
		// + "LEFT JOIN FETCH plal.articles plala " + "",
				Explored.class).getResultList();
	}

	public List<Explored> findAllExploreds(String lang) {

		final Query query = em
				.createQuery(
						"SELECT DISTINCT ex FROM Explored ex "
								+ "LEFT JOIN FETCH ex.discount di "
								+ "LEFT JOIN FETCH di.deal d "
								+ "LEFT JOIN FETCH ex.promotion pr "
								+ "LEFT JOIN FETCH pr.plan pl "
								+ "LEFT JOIN FETCH pl.product p "
								+ "LEFT JOIN FETCH d.product pd "
								+ "LEFT JOIN FETCH pd.articlesLocalization pdal "
								+ "LEFT JOIN FETCH pdal.articles pdala "
								+ "LEFT JOIN FETCH p.articlesLocalization pal "
								+ "LEFT JOIN FETCH pal.articles pala "
								+ "LEFT JOIN FETCH pl.articlesLocalization plal "
								+ "LEFT JOIN FETCH plal.articles plala "
								+ "WHERE (pala.lang=:lang AND plala.lang=:lang) OR pdala.lang=:lang"
								+ "", Explored.class);
		query.setParameter("lang", lang);
		try {
			return query.getResultList();
		} catch (Exception e) {

			e.getStackTrace();
			return null;
		}

	}

	public long countExploreds() {
		return em.createQuery("SELECT COUNT(ex)  FROM Explored ex", Long.class)
				.getSingleResult();
	}

	public List<Explored> findExploredsEntries(int firstResult, int maxResults) {
		return em.createQuery("SELECT ex FROM Explored ex", Explored.class)
				.setFirstResult(firstResult).setMaxResults(maxResults)
				.getResultList();
	}

	public Explored findExploredById(int id) {
		return em.find(Explored.class, id);
	}

	public void deleteExplored(int id) {
		final Explored explored = em.find(Explored.class, id);

		if (explored != null) {

			em.remove(explored);
		}
	}

	public Explored saveExplored(Explored explored) {
		System.out.println(explored);
		try {
			em.persist(explored);
		} catch (Exception e) {
			e.printStackTrace();
		}

		return explored;
	}

	public Explored updateExplored(Explored explored) {
		final Explored merged = em.merge(explored);
		return merged;
	}

	public long countExploreds(String lang) {
		final Query query = em.createQuery("SELECT COUNT(ex) FROM Explored ex "
				+ "LEFT JOIN  ex.productLocalization pal "
				+ "LEFT JOIN  pal.articles pala " + "WHERE pala.lang=:lang"
				+ "", Long.class);
		query.setParameter("lang", lang);
		return (Long) query.getSingleResult();
	}

	public List<Explored> findExploredsEntries(String lang, int firstResult,
			int maxResults) {
		final Query query = em.createQuery("SELECT ex FROM Explored ex "
				+ "INNER JOIN FETCH ex.productLocalization pal "
				+ "INNER JOIN FETCH ex.product p "
				+ "INNER JOIN pal.articles al "
				+ "INNER JOIN FETCH ex.media m " + "WHERE al.lang=:lang ");
		query.setParameter("lang", lang);
		query.setFirstResult(firstResult);
		query.setMaxResults(maxResults);
		List<Explored> exploreds = null;
		Set<Integer> idset = new HashSet<Integer>();
		try {
			exploreds = query.getResultList();

			for (Explored explored : exploreds) {
				idset.add(explored.getProductLocalization()
						.getArticlesLocalizationID());
			}
			try {
				List<ArticlesLocalization> list = em
						.createQuery(
								"SELECT al FROM ArticlesLocalization al INNER JOIN FETCH al.articles a "
										+ "WHERE a.lang=:lang AND al.articlesLocalizationID IN (:x)",
								ArticlesLocalization.class)
						.setParameter("lang", lang).setParameter("x", idset)
						.getResultList();
			} catch (Exception e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.getStackTrace();
		}
		return exploreds;

	}
	
	public List<Explored> findExploredByMerchants(Integer merchantID,String lang) {
		final Query query = em.createQuery("SELECT ex FROM Explored ex "
				+ "INNER JOIN FETCH ex.productLocalization pal "
				+ "INNER JOIN FETCH ex.product p " 
				+ "INNER JOIN FETCH p.merchant merch "
				+ "INNER JOIN pal.articles al "
				+ "INNER JOIN FETCH ex.media m " + "WHERE al.lang=:lang " 
				+ "AND merch.merchantID =:merchantID ");
		query.setParameter("lang", lang).setParameter("merchantID", merchantID);
		
		List<Explored> exploreds = null;
		Set<Integer> idset = new HashSet<Integer>();
		try {
			exploreds = query.getResultList();

			for (Explored explored : exploreds) {
				idset.add(explored.getProductLocalization()
						.getArticlesLocalizationID());
			}
			try {
				List<ArticlesLocalization> list = em
						.createQuery(
								"SELECT al FROM ArticlesLocalization al INNER JOIN FETCH al.articles a "
										+ "WHERE a.lang=:lang AND al.articlesLocalizationID IN (:x)",
								ArticlesLocalization.class)
						.setParameter("lang", lang).setParameter("x", idset)
						.getResultList();
			} catch (Exception e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.getStackTrace();
		}
		return exploreds;

	}
	

	public Explored findExploredByProduct(Integer productID) {
		try {
			return em
					.createQuery(
							"SELECT ex FROM Explored ex WHERE ex.product.productID=:id ",
							Explored.class).setParameter("id", productID)
					.getResultList().get(0);
		} catch (Exception e) {
			return null;
		}

	}

	public List<Explored> verifyExplored() {
		try {
			return em.createQuery(
					"SELECT ex FROM Explored ex " + "" + ""
							+ "WHERE ex.hasDeals=1 ", Explored.class)
					.getResultList();
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}

	}

	public Explored findExploredById(String lang, int id) {

		try {
			return em
					.createQuery(
							"SELECT ex FROM Explored ex "
									+ ""
									+ "LEFT JOIN FETCH ex.productLocalization pal "
									+ "LEFT JOIN FETCH pal.articles al "
									+ "WHERE ex.exploredID=:id AND  al.lang=:lang",
							Explored.class).setParameter("id", id)
					.setParameter("lang", lang).getResultList().get(0);
		} catch (Exception e) {
			// e.printStackTrace();
			return null;
		}
	}

	public List<Explored> findExploredsEntriesByDate(String lang,
			int firstResult, int maxResults, String order) {
		final Query query = em.createQuery("SELECT ex FROM Explored ex "
				+ "LEFT JOIN FETCH ex.productLocalization pal "
				+ "INNER JOIN FETCH ex.product p "
				+ "LEFT JOIN FETCH ex.media m " + "" + ""
				+ "INNER JOIN pal.articles al " + "WHERE al.lang=:lang "
				+ "ORDER BY ex.date " + order);
		query.setParameter("lang", lang);
		query.setFirstResult(firstResult);
		query.setMaxResults(maxResults);
		List<Explored> exploreds = null;
		Set<Integer> idset = new HashSet<Integer>();
		try {
			exploreds = query.getResultList();
			for (Explored explored : exploreds) {
				idset.add(explored.getProductLocalization()
						.getArticlesLocalizationID());
			}
			try {
				em.createQuery(
						"SELECT al FROM ArticlesLocalization al INNER JOIN FETCH al.articles a WHERE a.lang=:lang AND al.articlesLocalizationID IN (:x)",
						ArticlesLocalization.class).setParameter("lang", lang)
						.setParameter("x", idset).getResultList();
			} catch (Exception e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.getStackTrace();
		}
		return exploreds;
	}

	public List<Explored> findExploredsEntriesWithDeals(String lang,
			int firstResult, int maxResults, String order) {
		final Query query = em.createQuery("SELECT ex FROM Explored ex "
				+ "LEFT JOIN FETCH ex.productLocalization pal "
				+ "INNER JOIN FETCH ex.product p "
				+ "LEFT JOIN FETCH ex.media m " + "" + ""
				+ "INNER JOIN pal.articles al " + "WHERE al.lang=:lang "
				+ "AND ex.discountedPrice>0 ");
		query.setFirstResult(firstResult);
		query.setParameter("lang", lang);
		query.setMaxResults(maxResults);
		List<Explored> exploreds = null;
		Set<Integer> idset = new HashSet<Integer>();
		try {
			exploreds = query.getResultList();
			for (Explored explored : exploreds) {
				idset.add(explored.getProductLocalization()
						.getArticlesLocalizationID());
			}
			try {
				em.createQuery(
						"SELECT al FROM ArticlesLocalization al INNER JOIN FETCH al.articles a WHERE a.lang=:lang AND al.articlesLocalizationID IN (:x)",
						ArticlesLocalization.class).setParameter("lang", lang)
						.setParameter("x", idset).getResultList();
			} catch (Exception e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.getStackTrace();
		}
		return exploreds;
	}

	public List<Explored> findExploredsEntriesByTitle(String lang,
			int firstResult, int maxResults, String order) {
		final Query query = em.createQuery("SELECT ex FROM Explored ex "
				+ "LEFT JOIN FETCH ex.productLocalization pal "
				+ "INNER JOIN FETCH ex.product p "
				+ "LEFT JOIN FETCH ex.media m " + "INNER JOIN pal.articles al "
				+ "WHERE al.lang=:lang " + "" + "" + "ORDER BY ex.date "
				+ order);
		query.setFirstResult(firstResult);
		query.setParameter("lang", lang);
		query.setMaxResults(maxResults);
		List<Explored> exploreds = null;
		Set<Integer> idset = new HashSet<Integer>();
		try {
			exploreds = query.getResultList();
			for (Explored explored : exploreds) {
				idset.add(explored.getProductLocalization()
						.getArticlesLocalizationID());
			}
			try {
				em.createQuery(
						"SELECT al FROM ArticlesLocalization al INNER JOIN FETCH al.articles a WHERE a.lang=:lang AND al.articlesLocalizationID IN (:x)",
						ArticlesLocalization.class).setParameter("lang", lang)
						.setParameter("x", idset).getResultList();
			} catch (Exception e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.getStackTrace();
		}
		return exploreds;
	}

	public List<Explored> findExploredsEntriesByCateg(String lang,
			int firstResult, int maxResults, String order) {
		final Query query = em.createQuery("SELECT ex FROM Explored ex "
				+ "LEFT JOIN FETCH ex.productLocalization pal "
				+ "LEFT JOIN FETCH pal.categoriesLocalization pal "
				+ "LEFT JOIN FETCH ex.media m " + "" + "" + "Gou BY ex.date "
				+ order);
		query.setFirstResult(firstResult);
		query.setMaxResults(maxResults);
		List<Explored> exploreds = null;
		Set<Integer> idset = new HashSet<Integer>();
		try {
			exploreds = query.getResultList();
			for (Explored explored : exploreds) {
				idset.add(explored.getProductLocalization()
						.getArticlesLocalizationID());
			}
			try {
				em.createQuery(
						"SELECT al FROM ArticlesLocalization al INNER JOIN FETCH al.articles a WHERE a.lang=:lang AND al.articlesLocalizationID IN (:x)",
						ArticlesLocalization.class).setParameter("lang", lang)
						.setParameter("x", idset).getResultList();
			} catch (Exception e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.getStackTrace();
		}
		return exploreds;
	}

	public List<Explored> findExploredsEntriesByRating(String lang,
			int firstResult, int maxResults, String order) {
		final Query query = em.createQuery("SELECT ex FROM Explored ex "
				+ "LEFT JOIN FETCH ex.productLocalization pal "
				+ "INNER JOIN FETCH ex.product p "
				+ "LEFT JOIN FETCH ex.media m " + "" + ""
				+ "INNER JOIN pal.articles al " + "WHERE al.lang=:lang "
				+ "ORDER BY ex.claims " + order);
		query.setFirstResult(firstResult);
		query.setParameter("lang", lang);
		query.setMaxResults(maxResults);
		List<Explored> exploreds = null;
		Set<Integer> idset = new HashSet<Integer>();
		try {
			exploreds = query.getResultList();
			for (Explored explored : exploreds) {
				idset.add(explored.getProductLocalization()
						.getArticlesLocalizationID());
			}
			try {
				em.createQuery(
						"SELECT al FROM ArticlesLocalization al INNER JOIN FETCH al.articles a WHERE a.lang=:lang AND al.articlesLocalizationID IN (:x)",
						ArticlesLocalization.class).setParameter("lang", lang)
						.setParameter("x", idset).getResultList();
			} catch (Exception e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.getStackTrace();
		}
		return exploreds;
	}

	public List<Explored> findExploredsEntries(String lang, int firstResult,
			int maxResults, Integer categorie) {
		final Query query = em.createQuery("SELECT ex FROM Explored ex "
				+ "LEFT JOIN FETCH ex.productLocalization pal "
				+ "LEFT JOIN FETCH pal.categoriesLocalizations cl "
				+ "LEFT JOIN FETCH cl.categories c "
				+ "INNER JOIN FETCH ex.product p "
				+ "LEFT JOIN FETCH ex.media m " + "" + ""
				+ "INNER JOIN pal.articles al " + "WHERE al.lang=:lang "
				+ "AND c.categoryID=:cat");

		query.setFirstResult(firstResult);
		query.setParameter("lang", lang);
		query.setParameter("cat", categorie);
		query.setMaxResults(maxResults);
		List<Explored> exploreds = null;
		Set<Integer> idset = new HashSet<Integer>();
		try {
			exploreds = query.getResultList();
			for (Explored explored : exploreds) {
				idset.add(explored.getProductLocalization()
						.getArticlesLocalizationID());
			}
			try {
				em.createQuery(
						"SELECT al FROM ArticlesLocalization al INNER JOIN FETCH al.articles a WHERE a.lang=:lang AND al.articlesLocalizationID IN (:x)",
						ArticlesLocalization.class).setParameter("lang", lang)
						.setParameter("x", idset).getResultList();
			} catch (Exception e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.getStackTrace();
		}
		return exploreds;
	}

}
